package com.example.electricbicycle.controller;

import utils.DBUtils;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@WebServlet("/ReportServlet")
public class ReportServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");

        String reportType = request.getParameter("reportType");
        String statBy = request.getParameter("statBy");
        String startDate = request.getParameter("startDate");
        String endDate = request.getParameter("endDate");

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> reportData = new ArrayList<>();

        try {
            conn = DBUtils.getConnection();

            String tableName = "";
            String timeField = "";
            String userField = "user_id";

            switch (reportType) {
                case "purchase":
                    tableName = "purchase_order";
                    timeField = "create_time";
                    break;
                case "sale":
                    tableName = "sale_order";
                    timeField = "sale_time";
                    break;
                case "after_sale":
                    tableName = "after_sale_order";
                    timeField = "create_time";
                    break;
            }

            String sql = "";
            if ("user".equals(statBy)) {
                sql = "SELECT u.id, u.name, COUNT(*) AS order_count" +
                        " FROM " + tableName + " o" +
                        " JOIN user u ON o." + userField + " = u.id" +
                        " WHERE o." + timeField + " BETWEEN ? AND ?" +
                        " GROUP BY u.id, u.name";
            } else {
                sql = "SELECT DATE(o." + timeField + ") AS order_date, COUNT(*) AS order_count" +
                        " FROM " + tableName + " o" +
                        " WHERE o." + timeField + " BETWEEN ? AND ?" +
                        " GROUP BY DATE(o." + timeField + ")";
            }

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, startDate);
            pstmt.setString(2, endDate);
            rs = pstmt.executeQuery();

            int totalCount = 0;
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                if ("user".equals(statBy)) {
                    row.put("id", rs.getInt("id"));
                    row.put("name", rs.getString("name"));
                } else {
                    row.put("order_date", rs.getDate("order_date").toString());
                }
                int orderCount = rs.getInt("order_count");
                row.put("order_count", orderCount);
                reportData.add(row);
                totalCount += orderCount;
            }

            request.setAttribute("reportData", reportData);
            request.setAttribute("reportType", reportType);
            request.setAttribute("statBy", statBy);
            request.setAttribute("startDate", startDate);
            request.setAttribute("endDate", endDate);
            request.setAttribute("totalCount", totalCount);
            request.setAttribute("dataSize", reportData.size());

            request.getRequestDispatcher("/admin/report.jsp").forward(request, response);

        } catch (SQLException e) {
            throw new ServletException("数据库操作失败", e);
        } finally {
            DBUtils.close(conn, pstmt, rs);
        }
    }
}